<?php
/**
 * Created by PhpStorm.
 * User: tom_xia
 * Date: 2017/12/15
 * Time: 11:35
 */
class Excel
{
    public $creator = "央视新闻移动网";//excel生成者
    public $lastModifyBy = "央视新闻移动网";//最后修改的人
    public $title = "Office 2007 XLSX  Document";//标题
    public $subject = "Office 2007 XLSX  Document";
    public $description = "Test document for Office 2007 XLSX";//描述
    public $keywords = "office 2007";//关键字
    public $category = "Test result file";//类型


    private $output_stream = "php://output";//输出流，可以是文件，也可以是标准输出流
    private $php_excel = null;

    public function __construct()
    {
        include_once (rtrim(APPPATH,"/")."/third_lib/PHPExcel.php");
        $this->php_excel = new PHPExcel();
    }
    public  function initExcel()
    {
        $this->php_excel->getProperties()->setCreator($this->creator);
        $this->php_excel->getProperties()->setLastModifiedBy($this->lastModifyBy);
        $this->php_excel->getProperties()->setTitle($this->title);
        $this->php_excel->getProperties()->setDescription($this->description);
        $this->php_excel->getProperties()->setSubject($this->subject);
        $this->php_excel->getProperties()->setKeywords($this->keywords);
        $this->php_excel->getProperties()->setCategory($this->category);
        $this->php_excel->setActiveSheetIndex(0);
        $this->php_excel->getActiveSheet()->setTitle("sheet1");
    }
    public function setActiveSheetIndex($sheetIndex,$sheetName="")
    {
        $this->php_excel->setActiveSheetIndex($sheetIndex);
        if($sheetName == ""){
            $sheetName = "sheet".($sheetIndex+1);
        }
        $this->php_excel->getActiveSheet()->setTitle($sheetName);
    }
    public function createExcel(array &$title,array &$data)
    {
        include_once (rtrim(APPPATH,"/")."/third_lib/PHPExcel/Cell.php");

        //1.生成标题
        foreach ($title as $key=>&$val){
            $this->php_excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($key)."1", $val);
        }
        //2.设置内容
        foreach ($data as $key=>&$row) {
            foreach ($row as $key1 => &$val) {
                $this->php_excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($key1) . ($key + 2), self::removeEmojiNew($val));
            }
        }
        $objWriter = PHPExcel_IOFactory::createWriter($this->php_excel, 'Excel5');
        if($this->output_stream == "php://output"){
            header("Pragma: public");
            header("Expires: 0");
            header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
            header("Content-Type: application/vnd.ms-excel;");
            header("Content-Disposition:attachment;filename=".substr(md5(time()),16).".xls");
            header("Content-Transfer-Encoding:binary");
            header('Cache-Control: max-age=1');
        }
        $objWriter->save($this->output_stream);
    }
    public static function removeEmoji($text) {
        // Match Emoticons
        $regexEmoticons = '/[\x{1F600}-\x{1F64F}]/u';
        $clean_text = preg_replace($regexEmoticons, '', $text);
        // Match Miscellaneous Symbols and Pictographs
        $regexSymbols = '/[\x{1F300}-\x{1F5FF}]/u';
        $clean_text = preg_replace($regexSymbols, '', $clean_text);
        // Match Transport And Map Symbols
        $regexTransport = '/[\x{1F680}-\x{1F6FF}]/u';
        $clean_text = preg_replace($regexTransport, '', $clean_text);
        // Match Miscellaneous Symbols
        $regexMisc = '/[\x{2600}-\x{26FF}]/u';
        $clean_text = preg_replace($regexMisc, '', $clean_text);
        // Match Dingbats
        $regexDingbats = '/[\x{2700}-\x{27BF}]/u';
        $clean_text = preg_replace($regexDingbats, '', $clean_text);
        return $clean_text;
    }
    public static function  removeEmojiNew($value){
        if(preg_match("/^[A-Za-z\d]+([-_.][A-Za-z\d]+)*@([A-Za-z\d]+[-.])+[A-Za-z\d]{2,4}$/",$value)){
            return $value;
        }
        $value = preg_replace_callback('/[\xf0-\xf7].{3}/', function($r) { return '@E' . base64_encode($r[0]);},$value);

        $countt=substr_count($value,"@");
        for ($i=0; $i < $countt; $i++) {
            $c = stripos($value,"@");
            $value=substr($value,0,$c).substr($value,$c+10,strlen($value)-1);
        }
        $value = preg_replace_callback('/@E(.{6}==)/', function($r) {return base64_decode($r[1]);}, $value);
        return $value;
    }

}